package io;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.druid.pool.DruidDataSource;
import com.such.kit.Callable;
import com.such.kit.Logger;
import com.such.kit.datacast.CastCommons;
import com.such.kit.file.FileCommons;
import com.such.kit.file.bean.FileInfo;
import com.such.kit.io.bean.ExcelIOConfig;
import com.such.kit.io.simple.ExcelIOSimple;
import com.such.kit.io.source.DatabaseIOSource;
import com.such.kit.io.source.MemoryIOSource;
import com.such.kit.io.support.IOSource;
import com.such.kit.io.template.ExcelIOTemplate;
import com.such.kit.io.template.support.IOTemplate;
import com.such.kit.placeholder.PlaceholderCommons;
import com.such.kit.stream.StreamCommons;
import com.such.kit.string.StringCommons;
import com.such.kit.validate.ValidateCommons;

public class ExcelIO {

	private DruidDataSource dataSource = new DruidDataSource();
	private DatabaseIOSource databaseIOSource;
	private MemoryIOSource memoryIOSource;
	
	public ExcelIO() throws Exception {
		/*
		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		dataSource.setUrl("jdbc:oracle:thin:@10.0.1.169:1521:VMS");
		dataSource.setUsername("CRSDEVELOP");
		dataSource.setPassword("CRSDEVELOP");
		dataSource.setUrl("jdbc:oracle:thin:@10.0.1.83:1521:WO");
		dataSource.setUsername("jesdev");
		dataSource.setPassword("jesdev");
		this.databaseIOSource = new DatabaseIOSource(dataSource);
		this.memoryIOSource = new MemoryIOSource();
		*/
	}

	public void ioSimple() throws Exception {
		ExcelIOConfig config = new ExcelIOConfig();
		config.setSheetName("test");
		config.addColTemplate("标题 1", "#[field1]");
		config.addColTemplate("标题 2", "#[field2]");
		config.addColTemplate("标题 3", "#[field3]");
		config.setOutputExpression("#[SP]#[com.such.kit.io.sqlprovider.DefaultIOSqlProvider]#[test]");
		ExcelIOSimple io = new ExcelIOSimple(ExcelIOSimple.FILE_TYPE_XLSX, config);
		io.output(this.databaseIOSource, "D:/SUCH/Desktop", "out");
	}

	public void ioSimpleNh() throws Exception {
		ExcelIOConfig config = new ExcelIOConfig();
		config.setSheetName("主表");
		config.addColTemplate("序号", "#[sequence]");
		config.addColTemplate("提交时间", "#[date time]");
		config.addColTemplate("钉钉姓名", "#[name]");
		config.addColTemplate("钉钉部门", "#[department]");
		config.addColTemplate("个人生活照1张（限填1次）", "#[image]");
		config.addInputExpression("results");

		String basePath = "D:/SUCH/Desktop/nh_temp"; 
		ExcelIOSimple io = new ExcelIOSimple(ExcelIOSimple.FILE_TYPE_XLSX, config);
		io.input(this.memoryIOSource, FileCommons.appendPathNode(basePath, "data.xlsx"));
		List<Map<String, Object>> results = this.memoryIOSource.getInputResult("results");
		for (Map<String, Object> result : results) {
			String sequence = result.get("sequence").toString();
			String name = result.get("name").toString();
			String image = result.get("image").toString();
			for (FileInfo fileInfo : FileCommons.listFolder(FileCommons.appendPathNode(basePath, "files", sequence))) {
				FileCommons.copyFile(fileInfo.getPath(), 
						FileCommons.appendPathNode(basePath, StringCommons.merger(name, ".", FileCommons.getEnlargeName(image))));
			}
		}
	}

	public void ioTemplate() throws Exception {
		String basePath = getClass().getResource("").getPath();
		// IOTemplate excelIO = new ExcelIOTemplate(new FileInputStream(basePath + "SADII_Template.xls"));
		IOTemplate excelIO = new ExcelIOTemplate("D:/SUCH/Works/chinajes.tax.other.02/jes.srs/src/resources/srs_attachment_01.xls");
		/*
		excelIO.putArg(IOSource.AUTO_REPLACE_G_KEY_IDENTITY, IOSource.AUTO_REPLACE_G_KEY_IDENTITY);
		excelIO.input(this.databaseIOSource, basePath + "SADII_Resource.xls");
		for (String key : this.memoryIOSource.getInputKeys()) {
			System.out.println(this.memoryIOSource.getInputResult(key));
		}
		*/
		
		// excelIO.output(basePath, "crsResource");
	}

	public void ioTemplates() throws Exception {
		FileCommons.listFolder("D:/SUCH/Desktop/shi", new Callable<FileInfo, Void>() {
			@Override
			public Void call(FileInfo fileInfo) throws Exception {
				String basePath = getClass().getResource("").getPath();
				IOTemplate excelIO = new ExcelIOTemplate(new FileInputStream(basePath + "SADII_Template.xls"));
				excelIO.putArg(IOSource.AUTO_REPLACE_G_KEY_IDENTITY, IOSource.AUTO_REPLACE_G_KEY_IDENTITY);
				Logger.info(getClass(), fileInfo.getPath());
				excelIO.input(new DatabaseIOSource(dataSource), fileInfo.getPath());
				return null;
			}
		});
	}

	public void test() throws Exception {
		/*
		String from = "D:/SUCH/Desktop/srs_attachment_01.xls"; // D:/SUCH/Desktop/from.xlsx
		String to = "D:/SUCH/Desktop/to.xls";
		Workbook fromWorkbook = new HSSFWorkbook(new FileInputStream(from));
		Workbook toWorkbook = new HSSFWorkbook();
		ExcelIOSimple.copyWorkbook(fromWorkbook, toWorkbook);
		toWorkbook.write(new FileOutputStream(to));
		*/
		
		String path = "D:/SUCH/Works/chinajes.pa.other.01/reports/tyckr/000000/2022-06-30/1/表18：同一债权人信息及账户归户情况表（人民银行债权信息统计表）.xls";
		FileInputStream fileIn = new FileInputStream(path);
		HSSFWorkbook resourceWorkbook = new HSSFWorkbook(fileIn);
		Sheet resourceSheet = resourceWorkbook.getSheetAt(0);
		for (int i = 0; i < resourceSheet.getLastRowNum(); i++) {
			Row resourceRow = resourceSheet.getRow(i);
			if (ValidateCommons.isNotEmpty(resourceRow)) {
				for (int j = resourceRow.getFirstCellNum(); j < resourceRow.getLastCellNum(); j++) {
					Cell resourceCell = resourceRow.getCell(j);
					if (ValidateCommons.isNotEmpty(resourceCell)) {
						String field = ExcelIOSimple.getCellValue(resourceCell);
						System.out.println(field);
					}
				}
			}
		}
		StreamCommons.close(fileIn);
	}

	public void mergedRegions() throws Exception {
		String basePath = getClass().getResource("").getPath();
		IOTemplate excelIO = new ExcelIOTemplate(new FileInputStream(basePath + "excelTemplate.xlsx"));
		excelIO.output(databaseIOSource, basePath, "excelResource");
	}

	public static void main(String[] args) throws Exception {
		ExcelIO excelIO = new ExcelIO();
		System.out.println("开始：" + CastCommons.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss"));
		excelIO.test();
		System.out.println("完成：" + CastCommons.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss"));
	}

}
